﻿using System;
using System.Collections.Generic;
using System.Data;
using br.com.SistemaChamados.Enum;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.QueryManager.Core;
using br.com.SistemaChamados.QueryManager.Core.Behavior;

namespace br.com.SistemaChamados.DAO
{
    public class SectorDAO : GenericDao<Sector>
    {
        private static string SQL_UPDATE_SECTOR    = "UPDATE TBL_SECTOR SET TBL_SECTORDESCRIPTION=@sectordescription, TBL_UNITID=@unitid WHERE TBL_SECTORID=@sectorid";
        private static string SQL_DELETE_SECTOR    = " DELETE FROM TBL_SECTOR WHERE TBL_SECTORID = @id";
        private static string SQL_INSERT_SECTOR    = "INSERT INTO TBL_SECTOR (TBL_SECTORDESCRIPTION, TBL_UNITID) VALUES (@sectordescription, @unitid)";
        private static string SQL_GET_SECTORS_UNIT = " SELECT * FROM TBL_SECTOR WHERE TBL_UNITID=@unitid";


        public static EnumCRUDState UpdateSector(Sector sector)
        {
            EnumCRUDState resultSector = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@sectorid", sector.Id));
                command.Parameters.Add(Mapped.Parameter("@sectordescription", sector.Description));
                command.Parameters.Add(Mapped.Parameter("@unitid", sector.Unit.Id));
                command.ExecuteNonQuery();
                resultSector = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public static EnumCRUDState DeleteSector(Sector sector)
        {
            EnumCRUDState resultSector = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@id", sector.Id));
                command.ExecuteNonQuery();
                resultSector = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public static EnumCRUDState InsertSector(Sector sector)
        {
            EnumCRUDState resultSector = EnumCRUDState.SQL_NOTHING;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@sectordescription", sector.Description));
                command.Parameters.Add(Mapped.Parameter("@unitid", sector.Unit.Id));

                command.ExecuteNonQuery();
                resultSector = EnumCRUDState.SQL_SUCCESS;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public static Sector GetSector(int sectorId)
        {
            Sector resultSector = null;
            try
            {
                resultSector = (Sector)QueryManagerPerformer.Select(typeof(Sector), "Id", sectorId, ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION);
            }
            catch (Exception ex)
            {
                resultSector = null;
                throw ex;
            }
            return resultSector;
        }

        public static List<Sector> GetSectors()
        {
            List<Sector> list = new List<Sector>();
            try
            {
                list = QueryManagerPerformer.Select(typeof(Sector), ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION)
                        .ConvertAll(convertObject);
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
        }

        public static List<Sector> GetSectorsByUnit(Unit unit)
        {
            List<Sector> list = new List<Sector>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Sector resultSector = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_SECTORS_UNIT, conn);
                command.Parameters.Add(Mapped.Parameter("@unitid", unit.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultSector = new Sector();
                    unit = new Unit();
                    resultSector.Id = Convert.ToInt32(reader["TBL_SECTORID"]);
                    resultSector.Description = Convert.ToString(reader["TBL_SECTORDESCRIPTION"]);
                    unit.Id = Convert.ToInt32(reader["TBL_UNITID"]);
                    resultSector.Unit = unit;
                    list.Add(resultSector);
                }
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }
    }
}